﻿using Gumini.JulaDB.Enums;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// Class containing tests for the SQL UPDATE statement.
    /// </summary>
    [TestClass]
    public class UpdateTest
    {
        private static JulaDbConnection _connection;
        private static PersonTestDataCreator _peopleData;

        [TestInitialize]
        public void InitializeTest()
        {
            // database is initialized before every test to discard any changes made by previous tests
            _connection = TestUtils.CreateTestDatabase();
            _peopleData = new PersonTestDataCreator(_connection);
        }

        [TestCleanup]
        public void CleanupTest()
        {
            _connection.Close();
        }

        /// <summary>
        /// Update of a single value in a particular row.
        /// </summary>
        [TestMethod]
        public void UpdateSingleRow()
        {
            int rowsAffected = _connection.ExecuteValidNonQuery(@"update person set lastname = 'Malinowski' where id = 2");
            Assert.AreEqual(1, rowsAffected);

            _connection.ExecuteValidScalar(@"select count(*) from person where lastname = 'Malinowski'", 1);
        }

        /// <summary>
        /// Update of a single value in rows matching a WHERE condition.
        /// </summary>
        [TestMethod]
        public void UpdateTwoRows()
        {
            int rowsAffected = _connection.ExecuteValidNonQuery(@"update person set lastname = 'Malinowski' where firstname = 'Jan'");
            Assert.AreEqual(2, rowsAffected);

            _connection.ExecuteValidScalar(@"select count(*) from person where lastname = 'Malinowski'", 2);
        }

        /// <summary>
        /// Update all rows in a table.
        /// </summary>
        [TestMethod]
        public void UpdateAllRows()
        {
            int rowsAffected = _connection.ExecuteValidNonQuery(@"update person set lastname = 'Malinowski'");
            Assert.AreEqual(_peopleData.TotalPersonCount, rowsAffected);

            _connection.ExecuteValidScalar(@"select count(*) from person where lastname = 'Malinowski'", _peopleData.TotalPersonCount);
        }

        /// <summary>
        /// Attempt to update an nonexistent column should return an error.
        /// </summary>
        [TestMethod]
        public void UpdateUnknownColumn()
        {
            _connection.ExecuteInvalidCommand(@"update person set company = 'The Company'", ErrorCode.UnknownIdentifier);
        }

        /// <summary>
        /// Assigning a number to a string should be successful due to implicit conversion.
        /// </summary>
        [TestMethod]
        public void UpdateNumberToStringTypeConversion()
        {
            int rowsAffected = _connection.ExecuteValidNonQuery(@"update person set FirstName = 12.3 where id = 1");
        }

        /// <summary>
        /// Assigning string to a number should be successful in this case, as this string can be implicitly converted to a number.
        /// </summary>
        [TestMethod]
        public void UpdateStringToNumberTypeConversion()
        {
            int rowsAffected = _connection.ExecuteValidNonQuery(@"update person set Age = '12.3' where id = 1");
        }
    }
}
